package zy.dao.stock.check.impl;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.stock.check.CheckDAO;
import zy.dto.common.ProductDto;
import zy.dto.stock.check.CheckDiffDto;
import zy.entity.PageData;
import zy.entity.PageInfo;
import zy.entity.base.product.T_Base_Product;
import zy.entity.base.size.T_Base_Size;
import zy.entity.stock.T_Stock_Import;
import zy.entity.stock.check.T_Stock_Batch;
import zy.entity.stock.check.T_Stock_BatchScope;
import zy.entity.stock.check.T_Stock_Check;
import zy.entity.stock.check.T_Stock_CheckList;
import zy.entity.stock.data.T_Stock_DataBill;
import zy.entity.stock.data.T_Stock_DataView;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.StringUtil;

@Repository
public class CheckDAOImpl extends BaseDaoImpl implements CheckDAO{

	@Override
	public Integer batch_count(Map<String, Object> params) {
		Object ba_ar_state = params.get("ba_ar_state");
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object ba_dp_code = params.get("ba_dp_code");
		Object ba_manager = params.get("ba_manager");
		Object ba_number = params.get("ba_number");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_stock_batch t");
		sql.append(" WHERE 1=1");
		if (StringUtil.isNotEmpty(ba_ar_state)) {
			sql.append(" AND ba_ar_state = :ba_ar_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND ba_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND ba_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(ba_dp_code)) {
			sql.append(" AND ba_dp_code = :ba_dp_code ");
		}
		if (StringUtil.isNotEmpty(ba_manager)) {
			sql.append(" AND ba_manager = :ba_manager ");
		}
		if (StringUtil.isNotEmpty(ba_number)) {
			sql.append(" AND INSTR(ba_number,:ba_number) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public List<T_Stock_Batch> batch_list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object ba_ar_state = params.get("ba_ar_state");
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object ba_dp_code = params.get("ba_dp_code");
		Object ba_manager = params.get("ba_manager");
		Object ba_number = params.get("ba_number");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ba_id,ba_number,ba_date,ba_dp_code,ba_manager,ba_scope,ba_remark,ba_ar_state,ba_isexec,ba_execdate,ba_amount,ba_stockamount,");
		sql.append(" ba_costmoney,ba_retailmoney,ba_us_id,companyid,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = ba_dp_code AND dp.companyid = t.companyid LIMIT 1) AS depot_name,");
		sql.append(" (SELECT ck_number FROM t_stock_check ck WHERE ck_ba_number = ba_number AND ck_us_id = :us_id AND ck.companyid = t.companyid LIMIT 1) AS ck_number");
		sql.append(" FROM t_stock_batch t");
		sql.append(" WHERE 1=1");
		if (StringUtil.isNotEmpty(ba_ar_state)) {
			sql.append(" AND ba_ar_state = :ba_ar_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND ba_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND ba_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(ba_dp_code)) {
			sql.append(" AND ba_dp_code = :ba_dp_code ");
		}
		if (StringUtil.isNotEmpty(ba_manager)) {
			sql.append(" AND ba_manager = :ba_manager ");
		}
		if (StringUtil.isNotEmpty(ba_number)) {
			sql.append(" AND INSTR(ba_number,:ba_number) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY ba_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Stock_Batch.class));
	}
	
	@Override
	public T_Stock_Batch batch_load(Integer ba_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ba_id,ba_number,ba_date,ba_dp_code,ba_manager,ba_scope,ba_remark,ba_ar_state,ba_isexec,ba_execdate,ba_amount,ba_stockamount,");
		sql.append(" ba_costmoney,ba_retailmoney,ba_us_id,companyid,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = ba_dp_code AND dp.companyid = t.companyid LIMIT 1) AS depot_name,");
		sql.append(" (SELECT GROUP_CONCAT(bs_scope_code) FROM t_stock_batchscope bs WHERE bs_ba_number = ba_number AND bs.companyid = t.companyid) AS ba_scope_code,");
		sql.append(" (SELECT GROUP_CONCAT(bs_scope_name) FROM t_stock_batchscope bs WHERE bs_ba_number = ba_number AND bs.companyid = t.companyid) AS ba_scope_name");
		sql.append(" FROM t_stock_batch t");
		sql.append(" WHERE ba_id = :ba_id");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("ba_id", ba_id),
					new BeanPropertyRowMapper<>(T_Stock_Batch.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Stock_Batch check(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ba_id,ba_number,ba_dp_code,ba_manager,ba_scope,ba_remark,ba_ar_state,ba_isexec,ba_us_id,companyid,");
		sql.append(" (SELECT GROUP_CONCAT(bs_scope_code) FROM t_stock_batchscope bs WHERE bs_ba_number = ba_number AND bs.companyid = t.companyid) AS ba_scope_code");
		sql.append(" FROM t_stock_batch t");
		sql.append(" WHERE ba_number = :ba_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("ba_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Stock_Batch.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Stock_Batch check(String number,Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ba_id,ba_number,ba_scope,ba_ar_state,ba_isexec,ck_number");
		sql.append(" FROM t_stock_batch t");
		sql.append(" LEFT JOIN t_stock_check ck ON ck_ba_number = ba_number AND ck.companyid = t.companyid AND ck_us_id = :us_id");
		sql.append(" WHERE ba_number = :ba_number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("ba_number", number).addValue("us_id", us_id).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Stock_Batch.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public List<T_Stock_Batch> batch_check(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ba_scope,GROUP_CONCAT(bs_scope_code) AS ba_scope_code");
		sql.append(" FROM t_stock_batch t");
		sql.append(" LEFT JOIN t_stock_batchscope bs ON bs.bs_ba_number = t.ba_number AND bs.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND ba_dp_code = :ba_dp_code");
		sql.append(" AND (ba_ar_state = 0 OR (ba_ar_state = 1 AND ba_isexec = 0))");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY ba_number");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Stock_Batch.class));
	}

	@Override
	public void batch_save(T_Stock_Batch batch){
		String prefix = CommonUtil.NUMBER_PREFIX_STOCK_BATCH + DateUtil.getYearMonthDateYYYYMMDD();
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT CONCAT(:prefix,f_addnumber(MAX(ba_number))) AS new_number");
		sql.append(" FROM t_stock_batch");
		sql.append(" WHERE 1=1");
		sql.append(" AND INSTR(ba_number,:prefix) > 0");
		sql.append(" AND companyid = :companyid");
		String new_number = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("prefix", prefix).addValue("companyid", batch.getCompanyid()), String.class);
		batch.setBa_number(new_number);
		sql.setLength(0);
		sql.append(" INSERT INTO t_stock_batch");
		sql.append(" (ba_number,ba_date,ba_dp_code,ba_manager,ba_scope,ba_remark,ba_ar_state,ba_isexec,ba_amount,ba_stockamount,ba_costmoney,ba_retailmoney,ba_us_id,companyid)");
		sql.append(" VALUES");
		sql.append(" (:ba_number,:ba_date,:ba_dp_code,:ba_manager,:ba_scope,:ba_remark,:ba_ar_state,:ba_isexec,:ba_amount,:ba_stockamount,:ba_costmoney,:ba_retailmoney,:ba_us_id,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(batch),holder);
		batch.setBa_id(holder.getKey().intValue());
		if(batch.getBa_scope().intValue() == 0){//全场盘点无需保存子表，直接返回
			return;
		}
		String[] scopeCodes = batch.getBa_scope_code().split(",");
		String[] scopeNames = batch.getBa_scope_name().split(",");
		List<T_Stock_BatchScope> scopes = new ArrayList<T_Stock_BatchScope>();
		for (int i = 0; i < scopeCodes.length; i++) {
			T_Stock_BatchScope scope = new T_Stock_BatchScope();
			scope.setBs_ba_number(batch.getBa_number());
			scope.setBs_scope(batch.getBa_scope());
			scope.setBs_scope_code(scopeCodes[i]);
			scope.setBs_scope_name(scopeNames[i]);
			scope.setCompanyid(batch.getCompanyid());
			scopes.add(scope);
		}
		sql.setLength(0);
		sql.append("INSERT INTO t_stock_batchscope");
		sql.append(" (bs_ba_number,bs_scope,bs_scope_code,bs_scope_name,companyid)");
		sql.append(" VALUES(:bs_ba_number,:bs_scope,:bs_scope_code,:bs_scope_name,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(scopes.toArray()));
	}
	
	@Override
	public void batch_updateApprove(T_Stock_Batch batch) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_stock_batch");
		sql.append(" SET ba_ar_state=:ba_ar_state");
		sql.append(" WHERE ba_id=:ba_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(batch));
	}
	
	@Override
	public void batch_delete(String ba_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_stock_batch");
		sql.append(" WHERE ba_number=:ba_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("ba_number", ba_number).addValue("companyid", companyid));
		sql.setLength(0);
		sql.append(" DELETE FROM t_stock_batchscope");
		sql.append(" WHERE bs_ba_number=:bs_ba_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("bs_ba_number", ba_number).addValue("companyid", companyid));
	}
	
	@Override
	public List<T_Stock_Check> check_list4exec(String ba_number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ck_id,ck_number,ck_date,ck_amount,ck_stockamount,ck_us_id,ck_remark,");
		sql.append(" (SELECT us_name FROM t_sys_user us WHERE us_id = ck_us_id AND us.companyid = t.companyid LIMIT 1) AS us_name");
		sql.append(" FROM t_stock_check t");
		sql.append(" WHERE ck_ba_number = :ba_number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" UNION ALL");
		sql.append(" SELECT '' AS ck_id,'' AS ck_number, '' AS ck_date,0 AS ck_amount,0 AS ck_stockamount,ckl_us_id AS ck_us_id,'' AS ck_remark,");
		sql.append(" (SELECT us_name FROM t_sys_user us WHERE us_id = ckl_us_id AND us.companyid = t.companyid LIMIT 1) AS us_name");
		sql.append(" FROM t_stock_checklist_temp t");
		sql.append(" WHERE ckl_ba_number = :ba_number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY ckl_us_id");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("ba_number", ba_number).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Stock_Check.class));
	}
	
	@Override
	public T_Stock_Check check_load(String ck_number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ck_id,ck_ba_number,ck_number,ck_date,ck_dp_code,ck_manager,ck_remark,ck_isdraft,ck_isexec,ck_execdate,ck_amount,ck_stockamount,ck_money,ck_retailmoney,ck_us_id,companyid");
		sql.append(" FROM t_stock_check");
		sql.append(" WHERE ck_number = :ck_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("ck_number", ck_number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Stock_Check.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public List<T_Stock_CheckList> check_detail_list_all(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Integer ba_isexec = (Integer)params.get("ba_isexec");
		StringBuffer sql = new StringBuffer("");
		if(ba_isexec != null && ba_isexec.intValue() == 0){//未处理，查询盘点录入表
			sql.append(" SELECT ckl_id,ckl_pd_code,ckl_sub_code,ckl_szg_code,ckl_sz_code,ckl_cr_code,ckl_br_code,");
			sql.append(" SUM(ckl_amount) AS ckl_amount,ckl_stock_amount, ckl_unitprice,ckl_retailprice,ckl_remark,t.companyid,pd_no,pd_name,pd_unit,"); 
			sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = ckl_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name, ");
			sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = ckl_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name, ");
			sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = ckl_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name ");
			sql.append(" FROM t_stock_checklist t ");
			sql.append(" JOIN t_base_product pd ON pd_code = t.ckl_pd_code AND pd.companyid = t.companyid ");
			sql.append(" WHERE 1=1 ");
			sql.append(" AND ckl_ba_number = :ckl_ba_number ");
			sql.append(" AND t.companyid = :companyid ");
			sql.append(" GROUP BY ckl_sub_code");
		}else{//已处理，查询盘点结果子表
			sql.append(" SELECT ckl_id,ckl_pd_code,ckl_sub_code,ckl_szg_code,ckl_sz_code,ckl_cr_code,ckl_br_code,");
			sql.append(" ckl_amount,ckl_stock_amount, ckl_unitprice,ckl_retailprice,ckl_remark,t.companyid,pd_no,pd_name,pd_unit,"); 
			sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = ckl_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name, ");
			sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = ckl_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name, ");
			sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = ckl_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name ");
			sql.append(" FROM t_stock_checklist_all t ");
			sql.append(" JOIN t_base_product pd ON pd_code = t.ckl_pd_code AND pd.companyid = t.companyid ");
			sql.append(" WHERE 1=1 ");
			sql.append(" AND ckl_ba_number = :ckl_ba_number ");
			sql.append(" AND t.companyid = :companyid ");
		}
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY ckl_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Stock_CheckList.class));
	}
	
	@Override
	public List<T_Stock_CheckList> check_detail_sum_all(Map<String, Object> params) {
		Integer ba_isexec = (Integer)params.get("ba_isexec");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ckl_id,ckl_pd_code,ckl_szg_code,SUM(ckl_amount) AS ckl_amount,SUM(ckl_stock_amount) AS ckl_stock_amount,");
		sql.append(" ckl_unitprice,ckl_retailprice,ckl_remark,t.companyid,pd_no,pd_name,pd_unit,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name");
		if(ba_isexec != null && ba_isexec.intValue() == 0){//未处理，查询盘点录入表
			sql.append(" FROM t_stock_checklist t");
		}else{//已处理，查询盘点结果子表
			sql.append(" FROM t_stock_checklist_all t");
		}
		sql.append(" JOIN t_base_product pd ON pd_code = t.ckl_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND ckl_ba_number = :ckl_ba_number ");
		sql.append(" AND t.companyid = :companyid ");
		sql.append(" GROUP BY ckl_pd_code");
		sql.append(" ORDER BY ckl_pd_code ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Stock_CheckList.class));
	}
	
	@Override
	public List<String> check_detail_szgcode_all(Map<String, Object> params) {
		Integer ba_isexec = (Integer)params.get("ba_isexec");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT DISTINCT ckl_szg_code");
		if(ba_isexec != null && ba_isexec.intValue() == 0){//未处理，查询盘点录入表
			sql.append(" FROM t_stock_checklist t");
		}else{//已处理，查询盘点结果子表
			sql.append(" FROM t_stock_checklist_all t");
		}
		sql.append(" WHERE 1=1 ");
		sql.append(" AND ckl_ba_number = :ckl_ba_number ");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), params, String.class);
	}
	
	@Override
	public List<T_Stock_CheckList> check_detail_list_forsavetemp(String ck_number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ckl_id,ckl_number,ckl_ba_number,ckl_pd_code,ckl_sub_code,ckl_szg_code,ckl_sz_code,ckl_cr_code,ckl_br_code,ckl_amount,ckl_stock_amount,");
		sql.append(" ckl_unitprice,ckl_retailprice,ckl_remark,t.companyid");
		sql.append(" FROM t_stock_checklist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND ckl_number = :ckl_number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY ckl_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("ckl_number", ck_number).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Stock_CheckList.class));
	}
	
	@Override
	public List<T_Stock_CheckList> check_detail_list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ckl_id,ckl_number,ckl_ba_number,ckl_pd_code,ckl_sub_code,ckl_szg_code,ckl_sz_code,ckl_cr_code,ckl_br_code,ckl_amount,ckl_stock_amount,");
		sql.append(" ckl_unitprice,ckl_retailprice,ckl_remark,t.companyid,pd_no,pd_name,pd_unit,");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = ckl_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = ckl_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = ckl_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name");
		sql.append(" FROM t_stock_checklist t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.ckl_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND ckl_number = :ckl_number");
		sql.append(" AND t.companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY ckl_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Stock_CheckList.class));
	}
	
	@Override
	public List<T_Stock_CheckList> check_detail_sum(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ckl_id,ckl_number,ckl_pd_code,ckl_szg_code,SUM(ckl_amount) AS ckl_amount,SUM(ckl_stock_amount) AS ckl_stock_amount,");
		sql.append(" ckl_unitprice,ckl_retailprice,ckl_remark,t.companyid,pd_no,pd_name,pd_unit,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name");
		sql.append(" FROM t_stock_checklist t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.ckl_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND ckl_number = :ckl_number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY ckl_pd_code");
		sql.append(" ORDER BY ckl_pd_code ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Stock_CheckList.class));
	}
	
	@Override
	public List<String> check_detail_szgcode(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT DISTINCT ckl_szg_code");
		sql.append(" FROM t_stock_checklist t ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND ckl_number = :ckl_number");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), params, String.class);
	}
	
	@Override
	public List<T_Stock_CheckList> check_temp_list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ckl_id,ckl_ba_number,ckl_pd_code,ckl_sub_code,ckl_szg_code,ckl_sz_code,ckl_cr_code,ckl_br_code,ckl_amount,");
		sql.append(" ckl_unitprice,ckl_retailprice,ckl_remark,ckl_isrepair,ckl_us_id,t.companyid,pd_no,pd_name,pd_unit,");
		sql.append(" IFNULL((SELECT SUM(sd_amount) FROM t_stock_data sd WHERE sd_code = ckl_sub_code AND sd_dp_code = :dp_code AND sd.companyid = t.companyid),0) AS ckl_stock_amount,");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = ckl_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = ckl_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = ckl_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name");
		sql.append(" FROM t_stock_checklist_temp t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.ckl_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND ckl_ba_number = :ba_number");
		sql.append(" AND ckl_isrepair = :ckl_isrepair");
		sql.append(" AND ckl_us_id = :ckl_us_id");
		sql.append(" AND t.companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY ckl_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Stock_CheckList.class));
	}
	
	@Override
	public List<T_Stock_CheckList> check_temp_list_forimport(String ba_number,Integer ckl_isrepair,Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ckl_id,ckl_ba_number,ckl_pd_code,ckl_sub_code,ckl_szg_code,ckl_sz_code,ckl_cr_code,ckl_br_code,ckl_amount,ckl_stock_amount,");
		sql.append(" ckl_unitprice,ckl_retailprice,ckl_remark,ckl_isrepair,ckl_us_id,t.companyid");
		sql.append(" FROM t_stock_checklist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND ckl_ba_number = :ba_number");
		sql.append(" AND ckl_isrepair = :ckl_isrepair");
		sql.append(" AND ckl_us_id = :ckl_us_id");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource()
					.addValue("ba_number", ba_number)
					.addValue("ckl_isrepair", ckl_isrepair)
					.addValue("ckl_us_id", us_id)
					.addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Stock_CheckList.class));
	}
	
	@Override
	public List<T_Stock_CheckList> check_temp_list_forrepair(String ba_number,Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ckl_id,ckl_ba_number,ckl_pd_code,ckl_sub_code,ckl_szg_code,ckl_sz_code,ckl_cr_code,ckl_br_code,ckl_amount,ckl_stock_amount,");
		sql.append(" ckl_unitprice,ckl_retailprice,ckl_remark,ckl_isrepair,ckl_us_id,t.companyid");
		sql.append(" FROM t_stock_checklist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND ckl_ba_number = :ba_number");
		sql.append(" AND ckl_us_id = :ckl_us_id");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource()
					.addValue("ba_number", ba_number)
					.addValue("ckl_us_id", us_id)
					.addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Stock_CheckList.class));
	}
	
	@Override
	public List<T_Stock_CheckList> check_temp_list_forsave(String ba_number,String dp_code,Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ckl_id,ckl_ba_number,ckl_pd_code,ckl_sub_code,ckl_szg_code,ckl_sz_code,ckl_cr_code,ckl_br_code,ckl_amount,");
		sql.append(" ckl_unitprice,ckl_retailprice,ckl_remark,ckl_isrepair,ckl_us_id,t.companyid,");
		sql.append(" IFNULL((SELECT SUM(sd_amount) FROM t_stock_data sd WHERE sd_code = ckl_sub_code AND sd_dp_code = :dp_code AND sd.companyid = t.companyid),0) AS ckl_stock_amount");
		sql.append(" FROM t_stock_checklist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND ckl_ba_number = :ba_number");
		sql.append(" AND ckl_isrepair = :ckl_isrepair");
		sql.append(" AND ckl_us_id = :ckl_us_id");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY ckl_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource()
					.addValue("ba_number", ba_number)
					.addValue("dp_code", dp_code)
					.addValue("ckl_isrepair", 0)
					.addValue("ckl_us_id", us_id)
					.addValue("companyid", companyid),
		new BeanPropertyRowMapper<>(T_Stock_CheckList.class));
	}

	@Override
	public List<T_Stock_CheckList> check_temp_sum(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ckl_id,ckl_pd_code,ckl_szg_code,SUM(ckl_stock_amount) AS ckl_stock_amount,SUM(ckl_amount) AS ckl_amount,");
		sql.append(" ckl_unitprice,ckl_retailprice,ckl_remark,ckl_isrepair,ckl_us_id,companyid,pd_no,pd_name,pd_unit,bd_name,tp_name");
		sql.append(" FROM(");
		sql.append(" SELECT ckl_id,ckl_pd_code,ckl_szg_code,ckl_amount,");
		sql.append(" ckl_unitprice,ckl_retailprice,ckl_remark,ckl_isrepair,ckl_us_id,t.companyid,pd_no,pd_name,pd_unit,");
		sql.append(" IFNULL((SELECT SUM(sd_amount) FROM t_stock_data sd WHERE sd_code = ckl_sub_code AND sd_dp_code = :dp_code AND sd.companyid = t.companyid),0) AS ckl_stock_amount,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name");
		sql.append(" FROM t_stock_checklist_temp t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.ckl_pd_code AND pd.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND ckl_ba_number = :ba_number");
		sql.append(" AND ckl_isrepair = :ckl_isrepair");
		sql.append(" AND ckl_us_id = :ckl_us_id");
		sql.append(" AND t.companyid = :companyid");
		sql.append(")temp");
		sql.append(" GROUP BY ckl_pd_code");
		sql.append(" ORDER BY ckl_pd_code ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Stock_CheckList.class));
	}

	@Override
	public List<String> temp_szgcode(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT DISTINCT ckl_szg_code");
		sql.append(" FROM t_stock_checklist_temp t ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND ckl_ba_number = :ba_number");
		sql.append(" AND ckl_isrepair = :ckl_isrepair");
		sql.append(" AND ckl_us_id = :ckl_us_id");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), params, String.class);
	}
	
	@Override
	public T_Stock_CheckList check_temp_loadBySubCode(String sub_code,String ba_number,Integer ckl_isrepair, Integer us_id, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ckl_id,ckl_ba_number,ckl_pd_code,ckl_sub_code,ckl_szg_code,ckl_sz_code,ckl_cr_code,ckl_br_code,ckl_amount,");
		sql.append(" ckl_unitprice,ckl_retailprice,ckl_remark,ckl_isrepair,ckl_us_id,t.companyid");
		sql.append(" FROM t_stock_checklist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND ckl_ba_number = :ba_number");
		sql.append(" AND ckl_isrepair = :ckl_isrepair");
		sql.append(" AND ckl_sub_code = :ckl_sub_code");
		sql.append(" AND ckl_us_id = :ckl_us_id");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(),
					new MapSqlParameterSource()
							.addValue("ba_number", ba_number)
							.addValue("ckl_isrepair", ckl_isrepair)
							.addValue("ckl_sub_code", sub_code)
							.addValue("ckl_us_id", us_id)
							.addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Stock_CheckList.class));
		}catch(Exception e){
			return null;
		}
	}
	
	@Override
	public Integer count_product(Map<String, Object> param) {
		Object searchContent = param.get("searchContent");
		Object alreadyExist = param.get("alreadyExist");
		Object exactQuery = param.get("exactQuery");
		Integer ba_scope = (Integer)param.get("ba_scope");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT count(1)");
		sql.append(" FROM (");
		sql.append(" SELECT 1 FROM t_base_product t");
		sql.append(" LEFT JOIN t_stock_checklist_temp ckl ON ckl_pd_code = pd_code AND ckl.companyid = t.companyid AND ckl_us_id = :us_id");
		sql.append(" AND ckl_ba_number = :ba_number AND ckl_isrepair = :ckl_isrepair");
		sql.append(" where 1 = 1");
		if(ba_scope.intValue() == 1){//类别
			sql.append(" AND pd_tp_code IN(:scope_codes)");
		}else if(ba_scope.intValue() == 2){//品牌
			sql.append(" AND pd_bd_code IN(:scope_codes)");
		}else if(ba_scope.intValue() == 3){//单品
			sql.append(" AND pd_code IN(:scope_codes)");
		}else if(ba_scope.intValue() == 4){//年份
			sql.append(" AND pd_year IN(:scope_codes)");
		}else if(ba_scope.intValue() == 5){//季节
			sql.append(" AND pd_season IN(:scope_codes)");
		}
		if(StringUtil.isNotEmpty(searchContent)){
			if("1".equals(exactQuery)){
				sql.append(" AND (t.pd_name = :searchContent OR t.pd_no = :searchContent)");
			}else {
				sql.append(" AND (instr(t.pd_name,:searchContent)>0 OR instr(t.pd_spell,:searchContent)>0 OR instr(t.pd_no,:searchContent)>0)");
			}
        }
		if("1".equals(alreadyExist)){
			sql.append(" AND ckl_id IS NOT NULL ");
		}
		sql.append(" and t.companyid=:companyid");
		sql.append(" GROUP BY pd_code)t");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
	}

	@Override
	public List<T_Base_Product> list_product(Map<String, Object> param) {
		Object sidx = param.get(CommonUtil.SIDX);
		Object sord = param.get(CommonUtil.SORD);
		Object searchContent = param.get("searchContent");
		Object alreadyExist = param.get("alreadyExist");
		Object exactQuery = param.get("exactQuery");
		Integer ba_scope = (Integer)param.get("ba_scope");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pd_id,pd_code,pd_no,pd_name,IF(ckl_id IS NULL,0,1) AS exist");
		sql.append(" FROM t_base_product t");
		sql.append(" LEFT JOIN t_stock_checklist_temp ckl ON ckl_pd_code = pd_code AND ckl.companyid = t.companyid AND ckl_us_id = :us_id");
		sql.append(" AND ckl_ba_number = :ba_number AND ckl_isrepair = :ckl_isrepair");
		sql.append(" where 1 = 1");
		if(ba_scope.intValue() == 1){//类别
			sql.append(" AND pd_tp_code IN(:scope_codes)");
		}else if(ba_scope.intValue() == 2){//品牌
			sql.append(" AND pd_bd_code IN(:scope_codes)");
		}else if(ba_scope.intValue() == 3){//单品
			sql.append(" AND pd_code IN(:scope_codes)");
		}else if(ba_scope.intValue() == 4){//年份
			sql.append(" AND pd_year IN(:scope_codes)");
		}else if(ba_scope.intValue() == 5){//季节
			sql.append(" AND pd_season IN(:scope_codes)");
		}
		if(StringUtil.isNotEmpty(searchContent)){
			if("1".equals(exactQuery)){
				sql.append(" AND (t.pd_name = :searchContent OR t.pd_no = :searchContent)");
			}else {
				sql.append(" AND (instr(t.pd_name,:searchContent)>0 OR instr(t.pd_spell,:searchContent)>0 OR instr(t.pd_no,:searchContent)>0)");
			}
        }
		if("1".equals(alreadyExist)){
			sql.append(" AND ckl_id IS NOT NULL ");
		}
		sql.append(" and t.companyid=:companyid");
		sql.append(" GROUP BY pd_code");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY pd_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Product.class));
	}
	
	@Override
	public T_Base_Product load_product(String pd_code,Integer companyid){
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT pd.pd_id,pd.pd_code,pd_no,pd_name,pd_szg_code,pd_unit,pd_year,pd_season,pd_bd_code,pd_tp_code,pd_sell_price,pd_buy_price,pd_cost_price,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = pd.companyid LIMIT 1) AS pd_bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = pd.companyid LIMIT 1) AS pd_tp_name");
		sql.append(" ,(SELECT pdm_img_path FROM t_base_product_img pdm WHERE pdm_pd_code = pd.pd_code AND pdm.companyid = pd.companyid LIMIT 1) AS pdm_img_path");
		sql.append(" FROM t_base_product pd");
		sql.append(" WHERE 1=1");
		sql.append(" AND pd.pd_code = :pd_code");
		sql.append(" AND pd.companyid = :companyid");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("pd_code", pd_code).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Base_Product.class));
		}catch(Exception e){
			return null;
		}
	}
	
	@Override
	public Map<String, Object> load_product_size(Map<String,Object> params) {
		Map<String, Object> resultMap = new HashMap<String, Object>();
		//1.查询尺码信息
		List<T_Base_Size> sizes = namedParameterJdbcTemplate.query(getSizeSQL(), params, new BeanPropertyRowMapper<>(T_Base_Size.class));
		resultMap.put("sizes",sizes);
		//2.获取颜色杯型信息
		List<ProductDto> inputs = namedParameterJdbcTemplate.query(getColorBraSQL(), params, new BeanPropertyRowMapper<>(ProductDto.class));
		resultMap.put("inputs",inputs);
		//3.已录入数量
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ckl_sz_code AS sz_code,ckl_cr_code AS cr_code,ckl_br_code AS br_code,ckl_amount AS amount");
		sql.append(" FROM t_stock_checklist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND ckl_ba_number = :ba_number");
		sql.append(" AND ckl_isrepair = :ckl_isrepair");
		sql.append(" AND ckl_pd_code = :pd_code");
		sql.append(" AND ckl_us_id = :us_id");
		sql.append(" AND companyid = :companyid");
		List<ProductDto> temps = namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(ProductDto.class));
		resultMap.put("temps",temps);
		//4.库存数量
		List<ProductDto> stocks = namedParameterJdbcTemplate.query(getStockSQL(), params, new BeanPropertyRowMapper<>(ProductDto.class));
		resultMap.put("stocks",stocks);
		return resultMap;
	}

	@Override
	public void check_temp_save(List<T_Stock_CheckList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_stock_checklist_temp");
		sql.append(" (ckl_ba_number,ckl_pd_code,ckl_sub_code,ckl_szg_code,ckl_sz_code,ckl_cr_code,ckl_br_code,ckl_stock_amount,ckl_amount,");
		sql.append(" ckl_unitprice,ckl_retailprice,ckl_remark,ckl_isrepair,ckl_us_id,companyid)");
		sql.append(" VALUES(:ckl_ba_number,:ckl_pd_code,:ckl_sub_code,:ckl_szg_code,:ckl_sz_code,:ckl_cr_code,:ckl_br_code,:ckl_stock_amount,:ckl_amount,");
		sql.append(" :ckl_unitprice,:ckl_retailprice,:ckl_remark,:ckl_isrepair,:ckl_us_id,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}
	
	@Override
	public void check_temp_save(T_Stock_CheckList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_stock_checklist_temp");
		sql.append(" (ckl_ba_number,ckl_pd_code,ckl_sub_code,ckl_szg_code,ckl_sz_code,ckl_cr_code,ckl_br_code,ckl_stock_amount,ckl_amount,");
		sql.append(" ckl_unitprice,ckl_retailprice,ckl_remark,ckl_isrepair,ckl_us_id,companyid)");
		sql.append(" VALUES(:ckl_ba_number,:ckl_pd_code,:ckl_sub_code,:ckl_szg_code,:ckl_sz_code,:ckl_cr_code,:ckl_br_code,:ckl_stock_amount,:ckl_amount,");
		sql.append(" :ckl_unitprice,:ckl_retailprice,:ckl_remark,:ckl_isrepair,:ckl_us_id,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp),holder);
		temp.setCkl_id(holder.getKey().intValue());
	}

	@Override
	public void check_temp_update(List<T_Stock_CheckList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_stock_checklist_temp");
		sql.append(" SET ckl_amount = :ckl_amount");
		sql.append(" WHERE 1=1");
		sql.append(" AND ckl_ba_number = :ckl_ba_number");
		sql.append(" AND ckl_isrepair = :ckl_isrepair");
		sql.append(" AND ckl_sub_code = :ckl_sub_code");
		sql.append(" AND ckl_us_id = :ckl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
		
	}
	
	@Override
	public void check_temp_updateById(List<T_Stock_CheckList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_stock_checklist_temp");
		sql.append(" SET ckl_amount = :ckl_amount");
		sql.append(" WHERE 1=1");
		sql.append(" AND ckl_id = :ckl_id");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}
	
	@Override
	public void check_temp_update_repair(List<T_Stock_CheckList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_stock_checklist_temp");
		sql.append(" SET ckl_amount = :ckl_amount");
		sql.append(" ,ckl_isrepair = :ckl_isrepair");
		sql.append(" WHERE 1=1");
		sql.append(" AND ckl_id = :ckl_id");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}

	@Override
	public void check_temp_update(T_Stock_CheckList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_stock_checklist_temp");
		sql.append(" SET ckl_amount = :ckl_amount");
		sql.append(" WHERE 1=1");
		sql.append(" AND ckl_id = :ckl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}
	
	@Override
	public void check_temp_updateRemarkById(T_Stock_CheckList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_stock_checklist_temp");
		sql.append(" SET ckl_remark = :ckl_remark");
		sql.append(" WHERE 1=1");
		sql.append(" AND ckl_id = :ckl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}
	
	@Override
	public void check_temp_updateRemarkByPdCode(T_Stock_CheckList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_stock_checklist_temp");
		sql.append(" SET ckl_remark = :ckl_remark");
		sql.append(" WHERE 1=1");
		sql.append(" AND ckl_ba_number = :ckl_ba_number");
		sql.append(" AND ckl_isrepair = :ckl_isrepair");
		sql.append(" AND ckl_pd_code = :ckl_pd_code");
		sql.append(" AND ckl_us_id = :ckl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}
	
	@Override
	public void check_temp_del(List<T_Stock_CheckList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_stock_checklist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND ckl_ba_number = :ckl_ba_number");
		sql.append(" AND ckl_isrepair = :ckl_isrepair");
		sql.append(" AND ckl_sub_code = :ckl_sub_code");
		sql.append(" AND ckl_us_id = :ckl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}
	
	@Override
	public void check_temp_del(Integer ckl_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_stock_checklist_temp");
		sql.append(" WHERE ckl_id=:ckl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("ckl_id", ckl_id));
	}
	
	@Override
	public void check_temp_delByPiCode(T_Stock_CheckList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_stock_checklist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND ckl_ba_number = :ckl_ba_number");
		sql.append(" AND ckl_isrepair = :ckl_isrepair");
		sql.append(" AND ckl_pd_code = :ckl_pd_code");
		if(StringUtil.isNotEmpty(temp.getCkl_cr_code())){
			sql.append(" AND ckl_cr_code = :ckl_cr_code");
		}
		if(StringUtil.isNotEmpty(temp.getCkl_br_code())){
			sql.append(" AND ckl_br_code = :ckl_br_code");
		}
		sql.append(" AND ckl_us_id = :ckl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}
	
	@Override
	public void check_temp_clear(String ba_number,Integer ckl_isrepair,Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_stock_checklist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND ckl_ba_number = :ckl_ba_number");
		sql.append(" AND ckl_isrepair = :ckl_isrepair");
		sql.append(" AND ckl_us_id = :ckl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(),
				new MapSqlParameterSource().addValue("ckl_ba_number", ba_number)
						.addValue("ckl_isrepair", ckl_isrepair)
						.addValue("ckl_us_id", us_id)
						.addValue("companyid", companyid));
	}
	
	@Override
	public void check_temp_clear_all(String ba_number,Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_stock_checklist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND ckl_ba_number = :ckl_ba_number");
		sql.append(" AND ckl_us_id = :ckl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(),
				new MapSqlParameterSource().addValue("ckl_ba_number", ba_number).addValue("ckl_us_id", us_id).addValue("companyid", companyid));
	}
	
	@Override
	public List<T_Stock_Import> check_temp_listByImport(List<String> barCodes,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT DISTINCT bc_pd_code,bc_subcode,bc_barcode,bc_size,pd_szg_code,bc_color,bc_bra,pd_cost_price AS unit_price,pd_sell_price");
		sql.append(" FROM t_base_barcode bc");
		sql.append(" JOIN t_base_product pd ON pd.pd_code = bc_pd_code AND pd.companyid = bc.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND bc_barcode IN(:barcode)");
		sql.append(" AND bc.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("barcode", barCodes).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Stock_Import.class));
	}
	
	@Override
	public void check_save(T_Stock_Check check, List<T_Stock_CheckList> details) {
		String prefix = CommonUtil.NUMBER_PREFIX_STOCK_CHECK + DateUtil.getYearMonthDateYYYYMMDD();
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT CONCAT(:prefix,f_addnumber(MAX(ck_number))) AS new_number");
		sql.append(" FROM t_stock_check");
		sql.append(" WHERE 1=1");
		sql.append(" AND INSTR(ck_number,:prefix) > 0");
		sql.append(" AND companyid = :companyid");
		String new_number = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("prefix", prefix).addValue("companyid", check.getCompanyid()), String.class);
		check.setCk_number(new_number);
		sql.setLength(0);
		sql.append("INSERT INTO t_stock_check");
		sql.append(" (ck_ba_number,ck_number,ck_date,ck_dp_code,ck_manager,ck_remark,ck_isdraft,ck_isexec,ck_execdate,ck_amount,ck_stockamount,ck_money,ck_retailmoney,ck_us_id,companyid)");
		sql.append(" VALUES(:ck_ba_number,:ck_number,:ck_date,:ck_dp_code,:ck_manager,:ck_remark,:ck_isdraft,:ck_isexec,:ck_execdate,:ck_amount,:ck_stockamount,:ck_money,:ck_retailmoney,:ck_us_id,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(check),holder);
		check.setCk_id(holder.getKey().intValue());
		for(T_Stock_CheckList item:details){
			item.setCkl_number(check.getCk_number());
			item.setCkl_ba_number(check.getCk_ba_number());
		}
		sql.setLength(0);
		sql.append("INSERT INTO t_stock_checklist");
		sql.append(" (ckl_ba_number,ckl_number,ckl_pd_code,ckl_sub_code,ckl_szg_code,ckl_sz_code,ckl_cr_code,ckl_br_code,ckl_stock_amount,ckl_amount,ckl_unitprice,ckl_retailprice,ckl_remark,companyid)");
		sql.append(" VALUES(:ckl_ba_number,:ckl_number,:ckl_pd_code,:ckl_sub_code,:ckl_szg_code,:ckl_sz_code,:ckl_cr_code,:ckl_br_code,:ckl_stock_amount,:ckl_amount,:ckl_unitprice,:ckl_retailprice,:ckl_remark,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
		sql.setLength(0);
		sql.append("DELETE FROM t_stock_checklist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND ckl_ba_number = :ckl_ba_number");
		sql.append(" AND ckl_us_id = :ckl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(),
				new MapSqlParameterSource().addValue("ckl_ba_number", check.getCk_ba_number())
						.addValue("ckl_us_id", check.getCk_us_id())
						.addValue("companyid", check.getCompanyid()));
		
	}

	@Override
	public void check_delete(String ck_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_stock_check");
		sql.append(" WHERE 1=1");
		sql.append(" AND ck_number = :ck_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(),new MapSqlParameterSource().addValue("ck_number", ck_number).addValue("companyid", companyid));
		sql.setLength(0);
		sql.append("DELETE FROM t_stock_checklist");
		sql.append(" WHERE 1=1");
		sql.append(" AND ckl_number = :ckl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(),new MapSqlParameterSource().addValue("ckl_number", ck_number).addValue("companyid", companyid));
	}
	
	@Override
	public List<T_Stock_DataView> check_missing(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Integer ba_scope = (Integer)params.get("ba_scope");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT sd_id,sd_code,sd_cr_code,sd_sz_code,sd_br_code,sd_amount,pd_no,pd_name,pd_szg_code,pd_unit,");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = sd_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = sd_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = sd_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product pd ON pd_code = sd_pd_code AND t.companyid = pd.companyid");
		sql.append(" LEFT JOIN t_stock_checklist ckl ON ckl_sub_code = sd_code AND ckl_ba_number = :ba_number AND ckl.companyid = t.companyid"); 
		sql.append(" WHERE 1=1");
		if(ba_scope.intValue() == 1){//类别
			sql.append(" AND pd_tp_code IN(:scope_codes)");
		}else if(ba_scope.intValue() == 2){//品牌
			sql.append(" AND pd_bd_code IN(:scope_codes)");
		}else if(ba_scope.intValue() == 3){//单品
			sql.append(" AND pd_code IN(:scope_codes)");
		}else if(ba_scope.intValue() == 4){//年份
			sql.append(" AND pd_year IN(:scope_codes)");
		}else if(ba_scope.intValue() == 5){//季节
			sql.append(" AND pd_season IN(:scope_codes)");
		}
		sql.append(" AND sd_amount <> 0");
		sql.append(" AND ckl_id IS NULL");
		sql.append(" AND sd_dp_code = :dp_code");
		sql.append(" AND t.companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY sd_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Stock_DataView.class));
	}
	
	@Override
	public List<T_Stock_CheckList> check_detail_list_stockamount(String ba_number, String dp_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ckl_id,ckl_number,ckl_ba_number,ckl_pd_code,ckl_sub_code,ckl_szg_code,ckl_sz_code,ckl_cr_code,ckl_br_code,ckl_amount,");
		sql.append(" ckl_unitprice,ckl_retailprice,ckl_remark,t.companyid,");
		sql.append(" IFNULL((SELECT SUM(sd_amount) FROM t_stock_data sd WHERE sd_code = ckl_sub_code AND sd_dp_code = :dp_code AND sd.companyid = t.companyid),0) AS ckl_stock_amount");
		sql.append(" FROM t_stock_checklist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND ckl_ba_number = :ba_number");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("ba_number", ba_number).addValue("dp_code", dp_code).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Stock_CheckList.class));
	}
	
	@Override
	public void check_detail_updateStockAmount(List<T_Stock_CheckList> details) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_stock_checklist");
		sql.append(" SET ckl_stock_amount = :ckl_stock_amount");
		sql.append(" WHERE 1=1");
		sql.append(" AND ckl_id = :ckl_id");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public List<T_Stock_CheckList> check_missing_execall(Map<String, Object> params) {
		Integer ba_scope = (Integer)params.get("ba_scope");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT sd_pd_code AS ckl_pd_code,sd_code AS ckl_sub_code,pd_szg_code AS ckl_szg_code,sd_cr_code AS ckl_cr_code,sd_sz_code AS ckl_sz_code,");
		sql.append(" sd_br_code AS ckl_br_code,sd_amount AS ckl_stock_amount,pd_cost_price AS ckl_unitprice,pd_sell_price AS ckl_retailprice");
		sql.append(" FROM t_stock_data t");
		sql.append(" JOIN t_base_product pd ON pd_code = sd_pd_code AND t.companyid = pd.companyid");
		sql.append(" LEFT JOIN t_stock_checklist ckl ON ckl_sub_code = sd_code AND ckl_ba_number = :ba_number AND ckl.companyid = t.companyid"); 
		sql.append(" WHERE 1=1");
		if(ba_scope.intValue() == 1){//类别
			sql.append(" AND pd_tp_code IN(:scope_codes)");
		}else if(ba_scope.intValue() == 2){//品牌
			sql.append(" AND pd_bd_code IN(:scope_codes)");
		}else if(ba_scope.intValue() == 3){//单品
			sql.append(" AND pd_code IN(:scope_codes)");
		}else if(ba_scope.intValue() == 4){//年份
			sql.append(" AND pd_year IN(:scope_codes)");
		}else if(ba_scope.intValue() == 5){//季节
			sql.append(" AND pd_season IN(:scope_codes)");
		}
		sql.append(" AND sd_amount <> 0");
		sql.append(" AND ckl_id IS NULL");
		sql.append(" AND sd_dp_code = :dp_code");
		sql.append(" AND t.companyid = :companyid");
		
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Stock_CheckList.class));
	}
	
	@Override
	public void check_detail_all_save(List<T_Stock_CheckList> details) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_stock_checklist_all");
		sql.append(" (ckl_ba_number,ckl_pd_code,ckl_sub_code,ckl_szg_code,ckl_sz_code,ckl_cr_code,ckl_br_code,ckl_stock_amount,ckl_amount,ckl_unitprice,ckl_retailprice,ckl_remark,companyid)");
		sql.append(" VALUES(:ckl_ba_number,:ckl_pd_code,:ckl_sub_code,:ckl_szg_code,:ckl_sz_code,:ckl_cr_code,:ckl_br_code,:ckl_stock_amount,:ckl_amount,:ckl_unitprice,:ckl_retailprice,:ckl_remark,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void batch_update_exec(T_Stock_Batch batch){
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_stock_batch");
		sql.append(" SET ba_isexec = :ba_isexec,");
		sql.append(" ba_execdate = :ba_execdate,");
		sql.append(" ba_amount = :ba_amount,");
		sql.append(" ba_stockamount = :ba_stockamount,");
		sql.append(" ba_costmoney = :ba_costmoney,");
		sql.append(" ba_retailmoney = :ba_retailmoney");
		sql.append(" WHERE ba_id = :ba_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(batch));
	}
	
	@Override
	public void check_update_exec(Integer ck_isexec,String ck_execdate,String ba_number,Integer companyid){
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_stock_check t");
		sql.append(" SET ck_isexec = :ck_isexec,");
		sql.append(" ck_execdate = :ck_execdate,");
		sql.append(" ck_stockamount = (SELECT SUM(ckl_stock_amount) FROM t_stock_checklist ckl WHERE ckl_number = ck_number AND ckl.companyid = t.companyid)");
		sql.append(" WHERE ck_ba_number = :ba_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(),
				new MapSqlParameterSource().addValue("ck_isexec", ck_isexec)
						.addValue("ck_execdate", ck_execdate)
						.addValue("ba_number", ba_number)
						.addValue("companyid", companyid));
	}
	
	@Override
	public List<T_Stock_DataBill> listStock(String ba_number,String dp_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ckl_pd_code AS sd_pd_code,ckl_sub_code AS sd_code,ckl_cr_code AS sd_cr_code,ckl_sz_code AS sd_sz_code,");
		sql.append(" ckl_br_code AS sd_br_code,ckl_amount AS bill_amount,sd_id,sd_amount");
		sql.append(" FROM t_stock_checklist_all ckl");
		sql.append(" LEFT JOIN t_stock_data sd ON ckl_sub_code = sd_code AND ckl.companyid = sd.companyid AND sd.sd_dp_code = :dp_code");
		sql.append(" WHERE 1=1");
		sql.append(" AND ckl_ba_number = :ba_number");
		sql.append(" AND ckl.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("ba_number", ba_number).addValue("dp_code", dp_code).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Stock_DataBill.class));
	}
	
	private String getWhereSqlOfCheckDiff(Map<String,Object> params){
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object ba_dp_code = params.get("ba_dp_code");
		Object ba_number = params.get("ba_number");
		Object pd_no = params.get("pd_no");
		Object pd_name = params.get("pd_name");
		Object pd_tp_code = params.get("pd_tp_code");
		Object pd_bd_code = params.get("pd_bd_code");
		Integer loss = (Integer)params.get("loss");
		Integer overflow = (Integer)params.get("overflow");
		Integer flat = (Integer)params.get("flat");
		StringBuffer sql = new StringBuffer();
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND ba_execdate >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND ba_execdate <= :enddate ");
		}
		if (StringUtil.isNotEmpty(ba_dp_code)) {
			sql.append(" AND ba_dp_code = :ba_dp_code ");
		}
		if (StringUtil.isNotEmpty(ba_number)) {
			sql.append(" AND INSTR(ba_number,:ba_number) > 0 ");
		}
		if (StringUtil.isNotEmpty(pd_no)) {
			sql.append(" AND INSTR(pd_no,:pd_no) > 0 ");
		}
		if (StringUtil.isNotEmpty(pd_name)) {
			sql.append(" AND INSTR(pd_name,:pd_name) > 0 ");
		}
		if (StringUtil.isNotEmpty(pd_tp_code)) {
			params.put("tp_codes", Arrays.asList(pd_tp_code.toString().split(",")));
			sql.append(" AND pd_tp_code IN(:tp_codes) ");
		}
		if (StringUtil.isNotEmpty(pd_bd_code)) {
			params.put("bd_codes", Arrays.asList(pd_bd_code.toString().split(",")));
			sql.append(" AND pd_bd_code IN(:bd_codes) ");
		}
		Integer sum = loss + overflow + flat;
		if (sum.intValue() == 1) {//损、溢、平---选中1个
			if(loss.intValue() == 1){
				sql.append(" AND ckl_amount < ckl_stock_amount");
			}else if(overflow.intValue() == 1){
				sql.append(" AND ckl_amount > ckl_stock_amount");
			}else if(flat.intValue() == 1){
				sql.append(" AND ckl_amount = ckl_stock_amount");
			}
		}else if(sum.intValue() == 2){//损、溢、平---选中2个
			if(loss.intValue() == 0){
				sql.append(" AND ckl_amount >= ckl_stock_amount");
			}else if(overflow.intValue() == 0){
				sql.append(" AND ckl_amount <= ckl_stock_amount");
			}else if(flat.intValue() == 0){
				sql.append(" AND ckl_amount <> ckl_stock_amount");
			}
		}
		sql.append(" AND ba_isexec IN(1,2) ");
		sql.append(" AND t.companyid = :companyid ");
		return sql.toString();
	}
	
	@Override
	public Map<String, Object> check_diff_stat(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ");
		sql.append(" SUM(ckl_amount) AS ckl_amount,");
		sql.append(" SUM(ckl_stock_amount) AS ckl_stock_amount,");
		sql.append(" SUM(ckl_amount-ckl_stock_amount) AS sub_amount,");
		sql.append(" SUM(ckl_amount*ckl_unitprice) AS ckl_unitmoney,");
		sql.append(" SUM((ckl_amount-ckl_stock_amount)*ckl_unitprice) AS sub_costmoney,");
		sql.append(" SUM((ckl_amount-ckl_stock_amount)*ckl_retailprice) AS sub_retailmoney");
		sql.append(" FROM t_stock_checklist_all t ");
		sql.append(" JOIN t_stock_batch ba ON ba_number = ckl_ba_number AND ba.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = t.ckl_pd_code AND pd.companyid = t.companyid ");
		sql.append(" WHERE 1=1 ");
		sql.append(getWhereSqlOfCheckDiff(params));
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}
	
	@Override
	public PageData<CheckDiffDto> check_diff_list(Map<String,Object> params){
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Integer pageSize = (Integer)params.get(CommonUtil.PAGESIZE);
		Integer pageIndex = (Integer)params.get(CommonUtil.PAGEINDEX);
		params.put(CommonUtil.START, (pageIndex-1)*pageSize);
		params.put(CommonUtil.END, pageSize);
		StringBuffer sql = new StringBuffer("");
		//查询总个数
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_stock_checklist_all t ");
		sql.append(" JOIN t_stock_batch ba ON ba_number = ckl_ba_number AND ba.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = t.ckl_pd_code AND pd.companyid = t.companyid ");
		sql.append(" WHERE 1=1 ");
		sql.append(getWhereSqlOfCheckDiff(params));
		Integer totalCount = namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
		//查询结果集
		sql.setLength(0);
		sql.append(" SELECT ckl_id,ckl_pd_code,ckl_sub_code,ckl_szg_code,ckl_sz_code,ckl_cr_code,ckl_br_code,");
		sql.append(" ckl_amount,ckl_stock_amount, ckl_unitprice,ckl_retailprice,ckl_remark,t.companyid,pd_no,pd_name,pd_unit,"); 
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name,");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = ckl_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name, ");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = ckl_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name, ");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = ckl_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name ");
		sql.append(" ,ba_number,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = ba_dp_code AND dp.companyid = t.companyid LIMIT 1) AS depot_name");
		sql.append(" FROM t_stock_checklist_all t ");
		sql.append(" JOIN t_stock_batch ba ON ba_number = ckl_ba_number AND ba.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = t.ckl_pd_code AND pd.companyid = t.companyid ");
		sql.append(" WHERE 1=1 ");
		sql.append(getWhereSqlOfCheckDiff(params));
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY ckl_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		List<CheckDiffDto>list = namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(CheckDiffDto.class));
		PageInfo pageInfo = new PageInfo(totalCount, pageSize, pageIndex);
		PageData<CheckDiffDto> pageData = new PageData<CheckDiffDto>();
		pageData.setPageInfo(pageInfo);
		pageData.setList(list);
		return pageData;
	}
	
	@Override
	public PageData<CheckDiffDto> check_diff_sum(Map<String,Object> params){
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Integer pageSize = (Integer)params.get(CommonUtil.PAGESIZE);
		Integer pageIndex = (Integer)params.get(CommonUtil.PAGEINDEX);
		params.put(CommonUtil.START, (pageIndex-1)*pageSize);
		params.put(CommonUtil.END, pageSize);
		StringBuffer sql = new StringBuffer("");
		//查询总个数
		sql.append("SELECT COUNT(1) FROM");
		sql.append(" (SELECT 1");
		sql.append(" FROM t_stock_checklist_all t ");
		sql.append(" JOIN t_stock_batch ba ON ba_number = ckl_ba_number AND ba.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = t.ckl_pd_code AND pd.companyid = t.companyid ");
		sql.append(" WHERE 1=1");
		sql.append(getWhereSqlOfCheckDiff(params));
		sql.append(" GROUP BY ba_number,ckl_pd_code)temp");
		Integer totalCount = namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
		//查询结果集
		sql.setLength(0);
		sql.append(" SELECT ckl_id,ckl_pd_code,ckl_szg_code,SUM(ckl_amount) AS ckl_amount,SUM(ckl_stock_amount) AS ckl_stock_amount,");
		sql.append(" ckl_unitprice,ckl_retailprice,ckl_remark,t.companyid,pd_no,pd_name,pd_unit,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name");
		sql.append(" ,ba_number,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = ba_dp_code AND dp.companyid = t.companyid LIMIT 1) AS depot_name");
		sql.append(" FROM t_stock_checklist_all t ");
		sql.append(" JOIN t_stock_batch ba ON ba_number = ckl_ba_number AND ba.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = t.ckl_pd_code AND pd.companyid = t.companyid ");
		sql.append(" WHERE 1=1");
		sql.append(getWhereSqlOfCheckDiff(params));
		sql.append(" GROUP BY ba_number,ckl_pd_code");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY ckl_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		List<CheckDiffDto> list = namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(CheckDiffDto.class));
		PageInfo pageInfo = new PageInfo(totalCount, pageSize, pageIndex);
		PageData<CheckDiffDto> pageData = new PageData<CheckDiffDto>();
		pageData.setPageInfo(pageInfo);
		pageData.setList(list);
		return pageData;
	}
	
}
